/* Subversion Info: $Id: $ */

/* Drop the current Personnel table */
PROMPT Dropping table: PERSONNEL
set term off
DROP TABLE personnel CASCADE constraints;
set term on

/* Drop the current Personnel PK sequence */
PROMPT Dropping sequence: PERSONNEL_PK_SEQ
set term off
DROP SEQUENCE personnel_pk_seq;
set term on

/* Personnel PK sequence to be used for auto NUMBER PRIMARY KEY sequence */
PROMPT Creating sequence: PERSONNEL_PK_SEQ
set term off
CREATE SEQUENCE personnel_pk_seq 
INCREMENT BY 1
START WITH 1
NOCACHE;
set term on

/* Create the Personnel table */
PROMPT Creating table: PERSONNEL
set term off
CREATE TABLE personnel
  (
     id         NUMBER(3), 
     title      VARCHAR2(25) NOT NULL, 
     first_name VARCHAR2(25) NOT NULL, 
     last_name  VARCHAR2(50) NOT NULL,
     org		VARCHAR2(25),
     office		VARCHAR2(25),
     address1   VARCHAR2(100), 
     address2   VARCHAR2(100),
     city       VARCHAR2(100), 
     state      VARCHAR2(2), 
     zipcode    VARCHAR2(5), 
     phone      VARCHAR2(12) NOT NULL,
     email      VARCHAR2(100) NOT NULL,
     constraint personnel_pk primary key (id)
  );

GRANT SELECT, INSERT, UPDATE, DELETE ON ftdbadm.personnel TO ftdbuser;
set term on

/* Aircraft insert trigger */
PROMPT Creating trigger: PERSONNEL_PK_TRIG
set term off
CREATE OR REPLACE TRIGGER personnel_pk_trig 
  before INSERT ON personnel
  FOR each ROW
BEGIN
    SELECT personnel_pk_seq.nextval
    INTO   :new.id
    FROM   dual;
END;
/
set term on

/* Commit all installation changes */
set term off
COMMIT;
set term on

PROMPT
PROMPT Configuration Complete: PERSONNEL
PROMPT